﻿Imports MySql.Data.MySqlClient

Public Class addgroup2_2
    '-----------------------------------

    Public ds As Date = "#12/10/3000  09:00:00#"
    '-----------------------------------

    Public SQL As MySqlConnection

    Public key As String

    Dim newCourseid As String
    Dim count_before As Integer
    Dim SelectedEmployee As String

    Dim trainingHour As Integer

    Dim checkedApprove As Integer
    Public respone As Object
    Public test As String
    Public time_start As String
    Public time_end As String
    '---- Datetime Variable ---
    Dim date_split() As String
    Dim date_split2() As String
    Dim start_date_hh As String
    Dim start_date_mm As String
    Dim end_date_hh As String
    Dim end_date_mm As String
    Dim start_date As Date
    Dim end_date As Date
    Dim select_start_date As String
    Dim select_end_date As String



    Public Sub New(ByRef SQLCon As MySqlConnection, ByVal Cour_id As String)
        InitializeComponent()
        SQL = SQLCon
        newCourseid = Cour_id
        'MsgBox(newCourseid)
    End Sub


    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim NextForm As addgroup2_1 = New addgroup2_1(SQL)
        NextForm.Show()
        Me.Close()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        If Len(txt_CostPerPerson.Text) = 0 Or Len(txt_totalhr.Text) = 0 Or Len(txt_CostPerCourse.Text) = 0 Then
            MsgBox("Please Check field data", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Warning Message")
            Exit Sub
        End If
        respone = MsgBox("Are you sure to save data", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then
            '----- Check Amount of Index ------
            'Dim CheckIndex As Integer
            'Dim i As Integer
            'CheckIndex = ListView3.Items.Count
            'MsgBox(CheckIndex)
            '--------INSERT TO DATABASE ------
            'Add Code Here
            'For i = 0 To CheckIndex - 1
            Dim counter1 As Integer = 0

            If CheckBox1.Checked = True Then
                checkedApprove = 1
            Else
                checkedApprove = 0
            End If
            '------------------------------ Pawee Coding -------------------
            ''If Not ListView1.CheckedItems.Count = 0 Then
            ''While counter1 < ListView1.CheckedItems.Count
            ''Dim mySqlCommand As New MySqlCommand

            ''If SQL.State = ConnectionState.Closed Then
            ''SQL.Open()
            ''End If
            '  mySqlCommand.CommandText = "INSERT INTO Training VALUES ('" + selectedTrainee + "', '" + selectedCourse + +"', '" + DateTimePicker1.Value.ToString() + "', '" + NumericUpDown1.Value.ToString() + "', '" + NumericUpDown2.Value.ToString() + "', '" + NumericUpDown3.Value.ToString() + "." + NumericUpDown4.Value.ToString() + "','" + trainingHour + "','" + CheckBox1.Checked.ToString() + "');"

            'MsgBox("INSERT INTO training (emp_code,course_id,training_date,time_start,time_end,training_hours,training_approve) VALUES ('" & selectedTrainee & "', '" & selectedCourse & "', '" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "', '" & NumericUpDown1.Value & "." & NumericUpDown2.Value & "', '" & NumericUpDown3.Value & "." & NumericUpDown4.Value & "', '" & trainingHour & "'," & checkedApprove & ");")
            ''MySqlCommand.CommandText = "INSERT INTO training (emp_code,course_id,training_date,time_start,time_end,training_hours,training_approve,training_cost_person,training_type) VALUES ('" & ListView1.CheckedItems(counter1).Text & "', '" & newCourseid & "', '" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "','" & time_start & "', '" & time_end & "', '" & txt_totalhr.Text & "'," & checkedApprove & ",'" & txt_CostPerPerson.Text & "','" & cbo_CourseType.Text & "');"
            ''MsgBox(MySqlCommand.CommandText)
            ''MySqlCommand.CommandType = CommandType.Text
            ''MySqlCommand.Connection = SQL

            ''MySqlCommand.ExecuteNonQuery()

            ''counter1 = counter1 + 1

            ''SQL.Close()
            ''End While
            ''End If



            'GrabData()
            'GrabData2()

            ''--------------------- New Code --------------------
            If ListView3.Items.Count <> 0 Then
                Dim commandText As String
                Dim commandText2 As String
                Dim commandText3 As String
                Dim mySqlCommand As New MySqlCommand
                ' MsgBox(ListView3.Items.Count)
                ''------------------------------------------------
                commandText = "DELETE FROM Training where Course_id = " & newCourseid & ";"
                mySqlCommand.CommandText = commandText
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = SQL

                mySqlCommand.ExecuteNonQuery()
                SQL.Close()
                ''------------------------------------------------
                For counter1 = 0 To ListView3.Items.Count - 1

                    If SQL.State = ConnectionState.Closed Then
                        SQL.Open()
                    End If
                    '  mySqlCommand.CommandText = "INSERT INTO Training VALUES ('" + selectedTrainee + "', '" + selectedCourse + +"', '" + DateTimePicker1.Value.ToString() + "', '" + NumericUpDown1.Value.ToString() + "', '" + NumericUpDown2.Value.ToString() + "', '" + NumericUpDown3.Value.ToString() + "." + NumericUpDown4.Value.ToString() + "','" + trainingHour + "','" + CheckBox1.Checked.ToString() + "');"

                    'MsgBox("INSERT INTO training (emp_code,course_id,training_date,time_start,time_end,training_hours,training_approve) VALUES ('" & selectedTrainee & "', '" & selectedCourse & "', '" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "', '" & NumericUpDown1.Value & "." & NumericUpDown2.Value & "', '" & NumericUpDown3.Value & "." & NumericUpDown4.Value & "', '" & trainingHour & "'," & checkedApprove & ");")

                    commandText = "INSERT INTO training (emp_code,course_id,training_date,time_start,time_end,training_hours,training_approve,training_cost_person,training_type) VALUES ('" & ListView3.Items(counter1).Text & "', '" & newCourseid & "', '" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "','" & time_start & "', '" & time_end & "', '" & txt_totalhr.Text & "'," & checkedApprove & ",'" & txt_CostPerPerson.Text & "','" & ListView3.Items(counter1).SubItems(6).Text & "');"
                    mySqlCommand.CommandText = commandText
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = SQL

                    mySqlCommand.ExecuteNonQuery()
                    SQL.Close()
                Next counter1


                If SQL.State = ConnectionState.Closed Then
                    SQL.Open()
                End If
                commandText2 = "UPDATE Course SET course_cost = " + txt_CostPerCourse.Text + " , course_start_date = '" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "' WHERE course_id = " + newCourseid + ";"
                mySqlCommand.CommandText = commandText2
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = SQL

                mySqlCommand.ExecuteNonQuery()
                SQL.Close()
            End If

            '---------------------------------
            If CheckBox2.Checked Then
                Dim NextFrom As addgroup2_1 = New addgroup2_1(SQL)
                NextFrom.Show()
                Me.Close()
            Else
                Dim NextForm As main = New main()
                NextForm.Show()
                Me.Close()
            End If
        End If
        '---------------------------------------------------------------------------end Pawee
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim NextForm As main = New main()
        NextForm.Show()
        Me.Close()
    End Sub

    '  Private Sub addgroup2_2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    'Dim mainForm As main = New main()
    '   mainForm.Show()
    'Me.Close()
    ' End Sub

    Private Sub addgroup2_2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cbo_CourseType.Text = "Training Group"
        CheckBox1.Checked = True
        cbo_CourseType.Items.Add("Training Group")
        cbo_CourseType.Items.Add("On the job")
        ComboBox1.Text = "Name"
        cbo_CourseType.Text = "Training Group"
        ''DateTimePicker2.Value = Now
        Dim mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim mysqldr As MySql.Data.MySqlClient.MySqlDataReader

        Dim comsql As String = "SELECT * FROM Course where course_id = '" & newCourseid & "' ;"
        Dim adater As New MySqlCommand

        Try

            If SQL.State = ConnectionState.Closed Then
                SQL.Open()
            End If



            adater.CommandText = comsql
            adater.Connection = SQL
            mysqlda.SelectCommand = adater
            mysqldr = adater.ExecuteReader


            While (mysqldr.Read())
                TextBox2.Text = mysqldr("course_name")
                txt_CostPerCourse.Text = mysqldr("course_cost")


            End While

            mysqldr.Close()

        Catch ex As MySqlException
            MsgBox(ex.Message)
        End Try


        GrabData()
        SelectedEmployee = ""




    End Sub

    Private Sub GrabData()
        Dim mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim mysqldr As MySql.Data.MySqlClient.MySqlDataReader
       
        Dim comsql As String = "SELECT * FROM Training JOIN Employee JOIN Department where ( Employee.emp_code = Training.emp_code ) and ( Training.course_id like" + "'%" + newCourseid + "%'  ) and ( Employee.dept_id = Department.dept_id ) and emp_status = '1' ;"
        Dim comsql2 As String = "SELECT * FROM Course Where course_id = " + newCourseid + ""
        Dim adater As New MySqlCommand


        Try

            If SQL.State = ConnectionState.Closed Then
                SQL.Open()
            End If



            adater.CommandText = comsql
            adater.Connection = SQL
            mysqlda.SelectCommand = adater
            mysqldr = adater.ExecuteReader


            ListView3.Items.Clear()
            While (mysqldr.Read())
                With ListView3.Items.Add(mysqldr("emp_code"))
                    Try
                        .subItems.add(mysqldr("emp_gender"))
                    Catch
                        .subItems.add("-")
                    End Try
                    .subItems.add(mysqldr("emp_name") + " " + mysqldr("emp_surname"))
                    .subItems.add(mysqldr("dept_name"))
                    .subItems.add(mysqldr("dev_name"))
                    .subItems.add(mysqldr("emp_position"))
                    .subItems.add(mysqldr("training_type"))

                End With
                select_start_date = (mysqldr("time_start"))
                date_split = Split(select_start_date, ".")
                start_date_hh = date_split(0)
                start_date_mm = date_split(1)

                select_end_date = (mysqldr("time_end"))
                date_split2 = Split(select_end_date, ".")
                end_date_hh = date_split2(0)
                end_date_mm = date_split2(1)

                start_date = "#12/10/3000 " + start_date_hh + ":" + start_date_mm + ":00#"
                end_date = "#12/10/3000 " + end_date_hh + ":" + end_date_mm + ":00#"
                '------

                txt_CostPerPerson.Text = mysqldr("training_cost_person")

                DateTimePicker2.Value = start_date
                DateTimePicker3.Value = end_date
            End While
            '-------

            mysqldr.Close()

        Catch ex As MySqlException
            MsgBox(ex.Message)
        End Try

        count_before = ListView3.Items.Count

        Dim mysqlda2 As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim mysqldr2 As MySql.Data.MySqlClient.MySqlDataReader
        Dim adater2 As New MySqlCommand
        adater2.CommandText = comsql2
        adater2.Connection = SQL
        mysqlda2.SelectCommand = adater2
        mysqldr2 = adater2.ExecuteReader
        While (mysqldr2.Read())
            DateTimePicker1.Text = mysqldr2("course_start_date")
            txt_totalhr.Text = mysqldr2("course_duration")
        End While
        mysqldr2.Close()

    End Sub

    Private Sub GrabData2()
        Dim mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim mysqldr As MySql.Data.MySqlClient.MySqlDataReader



        '"Dim comsql As String = "SELECT * FROM Training JOIN Employee where ( Employee.emp_code = Training.emp_code ) and ( Training.course_id like" + "'%" + newCourseid + "%'  ) ;"
        ' Dim comsql As String = "SELECT * FROM Training JOIN Employee JOIN Department where ( Employee.emp_code = Training.emp_code ) and ( Training.course_id like" + "'%" + newCourseid + "%'  ) and ( Employee.dept_id = Department.dept_id ) ;"
        Dim comsql As String
        'If ComboBox1.SelectedItem.ToString() = "" Then

        'End If
        key = TextBox1.Text
        'MsgBox(key)
        If key = "'" Then
            ListView1.Items.Clear()
            Exit Sub
        End If


        If ComboBox1.SelectedIndex = 0 Then
            'comsql = "SELECT DISTINCT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and ( Employee.emp_name like '%" & TextBox1.Text & "%' or Employee.emp_surname like '%" & TextBox1.Text & "%' ) and emp_code NOT in ( SELECT Employee.emp_code from Employee JOIN Training JOIN Course where Employee.emp_code=Training.emp_code and Training.course_id = Course.course_id and Course.course_id = '" & newCourseid & "' ) Order by Employee.emp_name ;"
            'comsql = "SELECT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and ( Department.dept_name like" + "'%" + TextBox1.Text + "%' or Employee.emp_name like " + "'%" + TextBox1.Text + "%' or Employee.emp_surname like " + "'%" + TextBox1.Text + "%' or Employee.emp_level like " + "'%" + TextBox1.Text + "%'" + "or Employee.emp_code like " + "'%" + TextBox1.Text + "%'" + "or Employee.emp_position like " + "'%" + TextBox1.Text + "%'" + "or Employee.emp_gender like " + "'%" + TextBox1.Text + "%') order by Employee.emp_name;"
            comsql = "SELECT DISTINCT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and ( Employee.emp_name like '%" & TextBox1.Text & "%' or Employee.emp_surname like '%" & TextBox1.Text & "%' ) and emp_status = '1' Order by Employee.emp_name ;"
            'MsgBox("case 1")
            'MsgBox(comsql)
        Else
            If ComboBox1.SelectedIndex = 2 Then
                'comsql = "SELECT DISTINCT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and (Department.dept_name like '%" & TextBox1.Text & "%' ) and emp_code NOT in ( SELECT Employee.emp_code from Employee JOIN Training JOIN Course where Employee.emp_code=Training.emp_code and Training.course_id = Course.course_id and Course.course_id = '" & newCourseid & "' ) Order by Employee.emp_name;"
                comsql = "SELECT DISTINCT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and (Department.dept_name like '%" & TextBox1.Text & "%' ) and emp_status = '1' Order by Employee.emp_name;"
                'MsgBox("case 2")
                'MsgBox(comsql)
            Else
                If ComboBox1.SelectedIndex = 3 Then
                    'comsql = "SELECT DISTINCT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and (Department.dev_name like '%" & TextBox1.Text & "%' ) and emp_code NOT in ( SELECT Employee.emp_code from Employee JOIN Training JOIN Course where Employee.emp_code=Training.emp_code and Training.course_id = Course.course_id and Course.course_id = '" & newCourseid & "' ) Order by Employee.emp_name;"
                    comsql = "SELECT DISTINCT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and (Department.dev_name like '%" & TextBox1.Text & "%' ) and emp_status = '1' Order by Employee.emp_name;"
                    'MsgBox("case 3")
                    'MsgBox(comsql)
                Else
                    If ComboBox1.SelectedIndex = 1 Then
                        'comsql = "SELECT DISTINCT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and (Employee.emp_position like '%" & TextBox1.Text & "%' ) and emp_code NOT in ( SELECT Employee.emp_code from Employee JOIN Training JOIN Course where Employee.emp_code=Training.emp_code and Training.course_id = Course.course_id and Course.course_id = '" & newCourseid & "' ) Order by Employee.emp_name;"
                        comsql = "SELECT DISTINCT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and (Employee.emp_position like '%" & TextBox1.Text & "%' ) and emp_status = '1' Order by Employee.emp_name;"
                        '================================= ERROR HERE ========================
                        'MsgBox("case 4")
                        ' MsgBox(comsql)

                    Else
                        'comsql = "SELECT DISTINCT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and emp_code NOT in ( SELECT Employee.emp_code from Employee JOIN Training JOIN Course where Employee.emp_code=Training.emp_code and Training.course_id = Course.course_id and Course.course_id = '" & newCourseid & "' ) Order by Employee.emp_name;"
                        comsql = "SELECT DISTINCT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and emp_status = '1' Order by Employee.emp_name;"
                        'MsgBox("case 5")
                        'MsgBox(comsql)
                    End If

                End If
            End If
        End If






        Dim adater As New MySqlCommand
        Try

            adater.CommandText = comsql
            adater.Connection = SQL
            mysqlda.SelectCommand = adater
            mysqldr = adater.ExecuteReader

            ListView1.Items.Clear()
            While (mysqldr.Read())



                With ListView1.Items.Add(mysqldr("emp_code"))
                    Try
                        .subItems.add(mysqldr("emp_gender"))
                    Catch
                        .subItems.add("-")
                    End Try
                    .subItems.add(mysqldr("emp_name") + " " + mysqldr("emp_surname"))
                    .subItems.add(mysqldr("dept_name"))
                    .subItems.add(mysqldr("dev_name"))
                    .subItems.add(mysqldr("emp_position"))
                End With


            End While
            mysqldr.Close()

            'Catch ex As MySqlException
            'MsgBox(ex.Message)
        Catch
        End Try
    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        GrabData2()

    End Sub

    Private Sub ListView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick
        SelectedEmployee = ListView1.SelectedItems(0).Text
        'MsgBox(SelectedEmployee) <<< SelectedEmployee คือค่า Emp_code
    End Sub

    ' Private Sub ReCalcTime()
    '    If NumericUpDown1.Value * 60 + NumericUpDown3.Value > NumericUpDown2.Value * 60 + NumericUpDown4.Value Then
    '       trainingHour = 24 + (((NumericUpDown2.Value * 60 + NumericUpDown4.Value) - (NumericUpDown1.Value * 60 + NumericUpDown3.Value)) / 60)
    '  Else
    '     trainingHour = ((NumericUpDown2.Value * 60 + NumericUpDown4.Value) - (NumericUpDown1.Value * 60 + NumericUpDown3.Value)) / 60
    'End If
    'TextBox3.Text = trainingHour
    'End Sub
    ''----------------------------------- remove -------------------------
    ''Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


    ''Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    'Remove Code Here

    ''Dim counter1 As Integer = 0

    ''If Not ListView3.CheckedItems.Count = 0 Then
    ''   While counter1 < ListView3.CheckedItems.Count
    ''Dim mySqlCommand As New MySqlCommand

    ''  If SQL.State = ConnectionState.Closed Then
    ''    SQL.Open()
    ''  End If



    '  mySqlCommand.CommandText = "INSERT INTO Training VALUES ('" + selectedTrainee + "', '" + selectedCourse + +"', '" + DateTimePicker1.Value.ToString() + "', '" + NumericUpDown1.Value.ToString() + "', '" + NumericUpDown2.Value.ToString() + "', '" + NumericUpDown3.Value.ToString() + "." + NumericUpDown4.Value.ToString() + "','" + trainingHour + "','" + CheckBox1.Checked.ToString() + "');"

    'MsgBox("INSERT INTO training (emp_code,course_id,training_date,time_start,time_end,training_hours,training_approve) VALUES ('" & selectedTrainee & "', '" & selectedCourse & "', '" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "', '" & NumericUpDown1.Value & "." & NumericUpDown2.Value & "', '" & NumericUpDown3.Value & "." & NumericUpDown4.Value & "', '" & trainingHour & "'," & checkedApprove & ");")

    'mySqlCommand.CommandText = "INSERT INTO training (emp_code,course_id,training_date,time_start,time_end,training_hours,training_approve) VALUES ('" & ListView1.CheckedItems(counter1).Text & "', '" & newCourseid & "', '" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "', '" & NumericUpDown1.Value & "." & NumericUpDown3.Value & "', '" & NumericUpDown2.Value & "." & NumericUpDown4.Value & "', '" & trainingHour & "'," & checkedApprove & ");"
    '' mySqlCommand.CommandText = "DELETE FROM training where training_id = " & ListView3.CheckedItems(counter1).Text & ";"
    ''  mySqlCommand.CommandType = CommandType.Text
    '' mySqlCommand.Connection = SQL

    ''mySqlCommand.ExecuteNonQuery()

    ''counter1 = counter1 + 1

    ''SQL.Close()
    ''  End While
    ''End If




    ''GrabData()
    ''  GrabData2()
    ''End Sub



    'Private Sub NumericUpDown1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
    '    ReCalcTime()
    'End Sub

    'Private Sub NumericUpDown3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
    '   ReCalcTime()
    'End Sub

    'Private Sub NumericUpDown2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NumericUpDown2.ValueChanged
    '    ReCalcTime()
    'End Sub

    'Private Sub NumericUpDown4_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NumericUpDown4.ValueChanged
    '    ReCalcTime()
    'End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        'MsgBox(ComboBox1.SelectedItem.ToString())

    End Sub

    Private Sub TextBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyDown
        If e.KeyCode = "13" Then
            GrabData2()
        End If
    End Sub

    Private Sub DateTimePicker2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker2.ValueChanged
        RecalculateTime()
    End Sub
    Private Sub RecalculateTime()

        'Dim date1 As TimeSpan
        'Dim sum As String
        Dim dateTime As Date
        Dim dateTime2 As Date
        'Dim datetime_sub As Date
        'Dim hours As String
        Dim full_hours As Integer = 1440
        Dim int_hours As Integer
        Dim int_min As Integer

        'Dim result_hours As Integer
        Dim result_min As String

        Dim tempmin1 As Integer
        Dim tempmin2 As Integer

        Dim tempresultmin As Integer

        'datetime_sub = #12:00:00 AM#

        'Dim test As String
        dateTime = DateTimePicker2.Value
        dateTime2 = DateTimePicker3.Value

        test = dateTime2.Subtract(dateTime).ToString
        'MsgBox(test)

        Dim test1 As String
        test1 = Mid(test, 1, 1)
        ''MsgBox(test)
        '' MsgBox(test1)

        '' MsgBox(time_start)
        ''MsgBox(time_end)

        addZero(dateTime.Hour.ToString, dateTime.Minute.ToString, dateTime2.Hour.ToString, dateTime2.Minute.ToString)




        If test1 = "-" Then

            tempmin1 = CInt(dateTime.Hour.ToString()) * 60 + CInt(dateTime.Minute.ToString())
            tempmin2 = CInt(dateTime2.Hour.ToString()) * 60 + CInt(dateTime2.Minute.ToString())

            tempresultmin = tempmin2 - tempmin1

            tempresultmin = (24 * 60) + tempresultmin

            int_min = tempresultmin Mod 60

            int_hours = tempresultmin \ 60

            result_min = (dateTime.Minute - dateTime2.Minute)



            If (Len(int_hours.ToString) = 1) Then

                ''   MsgBox(txt_totalhr.Text)


                If (Len(int_min.ToString) = 1) Then

                    txt_totalhr.Text = "0" + int_hours.ToString + ".0" + int_min.ToString

                Else
                    txt_totalhr.Text = "0" + int_hours.ToString + "." + int_min.ToString
                End If


            ElseIf (Len(int_min.ToString) = 1) Then
                'addZero(int_min.ToString)
                txt_totalhr.Text = int_hours.ToString + ".0" + int_min.ToString

            Else

                txt_totalhr.Text = int_hours.ToString + "." + int_min.ToString


            End If


        Else

            tempmin1 = CInt(dateTime.Hour.ToString()) * 60 + CInt(dateTime.Minute.ToString())
            tempmin2 = CInt(dateTime2.Hour.ToString()) * 60 + CInt(dateTime2.Minute.ToString())

            tempresultmin = tempmin2 - tempmin1

            ''tempresultmin = tempresultmin + 60

            int_min = tempresultmin Mod 60

            int_hours = tempresultmin \ 60

            result_min = (dateTime2.Minute - dateTime.Minute)

            '' If (dateTime.Minute < dateTime2.Minute) Then

            ''If result_min > 30 Then
            ''int_hours = int_hours - 1
            ''End If
            ''End If

            If (Len(int_hours.ToString) = 1) Then

                ''   MsgBox(txt_totalhr.Text)


                If (Len(int_min.ToString) = 1) Then
                    txt_totalhr.Text = "0" + int_hours.ToString + ".0" + int_min.ToString

                Else
                    txt_totalhr.Text = "0" + int_hours.ToString + "." + int_min.ToString
                End If


            ElseIf (Len(int_min.ToString) = 1) Then
                'addZero(int_min.ToString)
                txt_totalhr.Text = int_hours.ToString + ".0" + int_min.ToString

            Else

                txt_totalhr.Text = int_hours.ToString + "." + int_min.ToString


            End If


            ''txt_totalhr.Text = test

        End If

        ''txt_totalhr.Text = trainingHour
        ''Label18.Text = trainingMin


    End Sub

    Public Sub addZero(ByVal hours1 As String, ByVal min1 As String, ByVal hours2 As String, ByVal min2 As String)

        If (Len(hours1) < 2) Then
            time_start = "0" + hours1
        Else
            time_start = hours1
        End If

        If (Len(min1) < 2) Then
            time_start = time_start + ".0" + min1
        Else
            time_start = time_start + "." + min1
        End If




        If (Len(hours2) < 2) Then
            time_end = "0" + hours2
        Else
            time_end = hours2
        End If
        If (Len(min2) < 2) Then
            time_end = time_end + ".0" + min2
        Else
            time_end = time_end + "." + min2
        End If
    End Sub

    Private Sub ListView1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseDoubleClick
        'ListView3.Items.Add(SelectedEmployee)
        'Dim Listview1_Index As Integer
        'Listview1_Index = ListView1.SelectedIndices(0)
        'ListView1.Items.RemoveAt(Listview1_Index)
        '----------------------
        '--Check Data Boolean--
        Dim CheckIndex As Integer
        Dim i As Integer
        Dim CheckData As Boolean
        CheckData = False
        CheckIndex = ListView3.Items.Count
        For i = 0 To CheckIndex - 1
            If SelectedEmployee <> ListView3.Items(i).Text Then
                'MsgBox("ไม่ซ้ำ")
                CheckData = True
            Else
                'MsgBox("ซ้ำ")
                CheckData = False
                Exit For
            End If
        Next i
        '----------------------
        If CheckData = False And CheckIndex <> 0 Then
            MsgBox("มีรายชื่อนี้อยู่แล้ว", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Warning Message")
        End If
        If CheckData = True Or CheckIndex = 0 Then
            Dim mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
            Dim mysqldr As MySql.Data.MySqlClient.MySqlDataReader
            Dim comsql As String
            comsql = "SELECT * FROM Employee JOIN Department where ( Employee.dept_id = Department.dept_id ) and ( Employee.emp_code like '%" & SelectedEmployee & "%') and emp_status = '1' ;"
            Dim adater As New MySqlCommand
            Try
                adater.CommandText = comsql
                adater.Connection = SQL
                mysqlda.SelectCommand = adater
                mysqldr = adater.ExecuteReader

                'ListView3.Items.Clear()
                While (mysqldr.Read())

                    With ListView3.Items.Add(mysqldr("emp_code"))
                        Try
                            .subItems.add(mysqldr("emp_gender"))
                        Catch
                            .subItems.add("-")
                        End Try
                        .subItems.add(mysqldr("emp_name") + " " + mysqldr("emp_surname"))
                        .subItems.add(mysqldr("dept_name"))
                        .subItems.add(mysqldr("dev_name"))
                        .subItems.add(mysqldr("emp_position"))
                        .subItems.add(cbo_CourseType.Text)
                    End With
                End While
                mysqldr.Close()

            Catch ex As MySqlException
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

    Private Sub ListView3_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView3.MouseDoubleClick
        Dim Listview3_Index As Integer
        Listview3_Index = ListView3.SelectedIndices(0)
        'ListView3.Items.RemoveAt(Listview3_Index)
        'SelectedEmployee = ListView3.SelectedItems(0).Text
        'MsgBox(SelectedEmployee)
        ListView3.Items.RemoveAt(Listview3_Index)
        '----------------------
    End Sub

    Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged

    End Sub

    Private Sub ListView3_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView3.SelectedIndexChanged

    End Sub

    Private Sub DateTimePicker3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker3.ValueChanged
        RecalculateTime()
    End Sub
End Class